package com.sqbang.dbcompare.util;

import com.sqbang.dbcompare.config.PathConfig;
import com.sqbang.dbcompare.pojo.dto.DbInfoRequest;
import com.sqbang.dbcompare.pojo.vo.ColumnVo;
import com.sqbang.dbcompare.pojo.vo.DifferentVo;
import com.sqbang.dbcompare.pojo.vo.TableVo;
import org.apache.commons.io.FileUtils;
import org.beetl.core.BeetlKit;
import org.springframework.util.CollectionUtils;

import java.io.File;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 对比两个数据库
 * @author suqiongbang
 * @date 2020/12/28 11:27
 */
public class CompareDatabaseUtil {

    private final String CHANGE = "更改";
    private final String MISS = "缺失";
    private final String NEEDLESS = "多余";

    private volatile static CompareDatabaseUtil instance;

    /**
     * 采用单例模式获取实例
     * @return
     */
    public static CompareDatabaseUtil getInstance() {
        //第一次判空,提高效率
        if (null == instance) {
            //保证线程安全
            synchronized (CompareDatabaseUtil.class) {
                //第二次判空,保证单例对象的唯一性,防止第一次有多个线程进入第一个if判断
                if (null == instance) {
                    instance = new CompareDatabaseUtil();
                }
            }
        }
        return instance;
    }

    /**
     * 创建对比报告
     * @param request
     * @throws Exception
     */
    public void create(DbInfoRequest request, String templatePath, String reportPath) throws Exception {
        List<TableVo> mainTableList = getTableStructure(request.getStandardDriver(), request.getStandardUrl(),
                request.getStandardUserName(), request.getStandardPassword());
        List<TableVo> subTableList = getTableStructure(request.getCompareDriver(), request.getCompareUrl(),
                request.getCompareUserName(), request.getComparePassword());
        List<DifferentVo> result = getDifferent(mainTableList, subTableList);
        createReport(result, templatePath, reportPath);
    }

    /**
     * 获取所有表结构
     *
     * @param driver   driver
     * @param url      url
     * @param user     user
     * @param password password
     * @throws Exception exception
     */
    private List<TableVo> getTableStructure(String driver, String url, String user, String password) throws Exception {
        List<TableVo> tableList = new ArrayList<>();
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        DatabaseMetaData metaData = connection.getMetaData();
        // 获取所有表，order前缀的表
        // ResultSet tableResultSet = metaData.getTables(null, null, "order%", new String[]{"TABLE"});
        ResultSet tableResultSet = metaData.getTables(null, null, "", new String[]{"TABLE"});
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            String tableRemarks = tableResultSet.getString("REMARKS");
            TableVo tableVo = new TableVo();
            tableVo.setTableName(tableName);
            tableVo.setRemark(tableRemarks);
            List<ColumnVo> columnList = new ArrayList<>();
            // 获取表字段结构
            ResultSet columnResultSet = metaData.getColumns(null, "%", tableName, "%");
            while (columnResultSet.next()) {
                // 字段名称
                String columnName = columnResultSet.getString("COLUMN_NAME");
                // 数据类型
                String columnType = columnResultSet.getString("TYPE_NAME");
                // 字段长度
                int datasize = columnResultSet.getInt("COLUMN_SIZE");
                // 小数部分位数
                int digits = columnResultSet.getInt("DECIMAL_DIGITS");
                // 是否可为空 1代表可空 0代表不可为空
                int nullable = columnResultSet.getInt("NULLABLE");
                // 默认值
                String columnDefault = columnResultSet.getString("COLUMN_DEF");
                // 描述
                String remarks = columnResultSet.getString("REMARKS");

                ColumnVo columnVo = new ColumnVo();
                columnVo.setColumnName(columnName);
                columnVo.setColumnType(columnType);
                columnVo.setDatasize(datasize);
                columnVo.setDigits(digits);
                columnVo.setIsNullable(nullable);
                columnVo.setRemarks(remarks);
                columnVo.setDefaultValue(columnDefault);
                columnList.add(columnVo);
            }
            tableVo.setColumnList(columnList);
            tableList.add(tableVo);
        }
        return tableList;
    }

    /**
     * 比较两个库的表结构差异
     * @param mainTableList
     * @param subTableList
     * @return
     */
    private List<DifferentVo> getDifferent(List<TableVo> mainTableList, List<TableVo> subTableList){
        List<DifferentVo> differentList = new ArrayList<>();
        Map<String, TableVo> subTableMap = subTableList.stream().collect(Collectors.toMap(TableVo::getTableName, Function.identity(),(key1, key2)->key2));
        for (TableVo table : mainTableList) {
            TableVo subTable = subTableMap.get(table.getTableName());
            if (subTable != null) {
                subTable.setIsBothHas(1);
                table.setIsBothHas(1);

                DifferentVo different = new DifferentVo();
                different.setTableName(table.getTableName());
                if (!table.getRemark().equalsIgnoreCase(subTable.getRemark())) {
                    different.setChangeType(CHANGE);
                    different.setIsRemarkChange("（该表的描述有改变）");
                }
                List<ColumnVo> columnList = subTable.getColumnList();
                if (!CollectionUtils.isEmpty(columnList)) {
                    Map<String, ColumnVo> subColumnMap = columnList.stream().collect(Collectors.toMap(ColumnVo::getColumnName, Function.identity(),(key1, key2)->key2));
                    if (!CollectionUtils.isEmpty(table.getColumnList())) {
                        List<DifferentVo.ColumnInfo> columnInfoList = new ArrayList<>();
                        for (ColumnVo column : table.getColumnList()) {
                            ColumnVo subColumn = subColumnMap.get(column.getColumnName());
                            if (subColumn != null) {
                                column.setIsBothHas(1);
                                subColumn.setIsBothHas(1);

                                DifferentVo.ColumnInfo columnInfo = new DifferentVo.ColumnInfo();
                                columnInfo.setColumnName(column.getColumnName());
                                boolean isChangeTable = false;
                                if (!column.getColumnType().equalsIgnoreCase(subColumn.getColumnType())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeColumnType("*");
                                }
                                if (!column.getDatasize().equals(subColumn.getDatasize())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeDatasize("*");
                                }
                                if (!column.getDigits().equals(subColumn.getDigits())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeDigits("*");
                                }
                                if (!column.getIsNullable().equals(subColumn.getIsNullable())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeNullable("*");
                                }
                                if (column.getDefaultValue() != null && !column.getDefaultValue().equalsIgnoreCase(subColumn.getDefaultValue())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeDefaultValue("*");
                                } else if (column.getDefaultValue() == null && subColumn.getDefaultValue() != null) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeDefaultValue("*");
                                }
                                if (!column.getRemarks().equalsIgnoreCase(subColumn.getRemarks())) {
                                    isChangeTable = true;
                                    columnInfo.setIsChangeRemark("*");
                                }

                                if (isChangeTable) {
                                    columnInfo.setChangeType(CHANGE);
                                    columnInfoList.add(columnInfo);
                                }
                            }
                        }
                        for (ColumnVo columnVo : table.getColumnList()) {
                            if (columnVo.getIsBothHas() != null && !columnVo.getIsBothHas().equals(1)) {
                                DifferentVo.ColumnInfo columnInfo = new DifferentVo.ColumnInfo();
                                columnInfo.setColumnName(columnVo.getColumnName());
                                columnInfo.setChangeType(MISS);
                                columnInfoList.add(columnInfo);
                            }
                        }
                        for (ColumnVo columnVo : subTable.getColumnList()) {
                            if (columnVo.getIsBothHas() != null && !columnVo.getIsBothHas().equals(1)) {
                                DifferentVo.ColumnInfo columnInfo = new DifferentVo.ColumnInfo();
                                columnInfo.setColumnName(columnVo.getColumnName());
                                columnInfo.setChangeType(NEEDLESS);
                                columnInfoList.add(columnInfo);
                            }
                        }
                        if (!CollectionUtils.isEmpty(columnInfoList)) {
                            different.setChangeType(CHANGE);
                            different.setColumnInfoList(columnInfoList);
                        }
                    }
                }
                differentList.add(different);
            }
        }
        for (TableVo table : mainTableList) {
            if (table.getIsBothHas() != null && !table.getIsBothHas().equals(1)) {
                DifferentVo different = new DifferentVo();
                different.setTableName(table.getTableName());
                different.setChangeType(MISS);
                differentList.add(different);
            }
        }
        for (TableVo table : subTableList) {
            if (table.getIsBothHas() != null && !table.getIsBothHas().equals(1)) {
                DifferentVo different = new DifferentVo();
                different.setTableName(table.getTableName());
                different.setChangeType(NEEDLESS);
                differentList.add(different);
            }
        }
        return differentList;
    }

    private void createReport(List<DifferentVo> data, String templatePath, String reportPath){
        List<DifferentVo> missTableList = data.stream().filter(e -> MISS.equals(e.getChangeType())).collect(Collectors.toList());
        List<DifferentVo> needlessTableList = data.stream().filter(e -> NEEDLESS.equals(e.getChangeType())).collect(Collectors.toList());
        List<DifferentVo> changeTableList = data.stream().filter(e -> CHANGE.equals(e.getChangeType())).collect(Collectors.toList());

        Map<String, Object> paraMap = new HashMap<String, Object>(4);
        paraMap.put("missTableList", missTableList);
        paraMap.put("needlessTableList", needlessTableList);
        paraMap.put("changeTableList", changeTableList);

        createFileByTemplete(templatePath, paraMap, reportPath);
    }

    /**
     * 根据具体模板生成文件
     * @param templateFileName	模板文件名称
     * @param paraMap			渲染参数
     * @param filePath			输出目录
     */
    private void createFileByTemplete(String templateFileName, Map<String, Object> paraMap, String filePath)  {
        try {
            String template = FileUtils.readFileToString(new File(templateFileName), "UTF-8");
            String javaSrc = BeetlKit.render(template, paraMap);
            File file = new File(filePath);
            if (!file.exists()) {
                file.getParentFile().mkdirs();
            }
            FileUtils.writeStringToFile(file, javaSrc, "UTF-8");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
